package drive.Mapper;

import drive.Model.Secondhanditem;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface SecondHandItemMapper {
    // 查询所有二手物品
    @Select("SELECT * FROM secondhanditem")
    List<Secondhanditem> findAll();

    // 根据itemID查询二手物品
    @Select("SELECT * FROM secondhanditem WHERE itemID = #{itemID}")
    Secondhanditem findByItemID(int itemID);

    // 根据sellerID查询二手物品
    @Select("SELECT * FROM secondhanditem WHERE sellerID = #{sellerID}")
    List<Secondhanditem> findBySellerID(int sellerID);

    // 新增二手物品
    @Insert("INSERT INTO secondhanditem (sellerID, title, description, price, creationTime, status) VALUES (#{sellerID}, #{title}, #{description}, #{price}, #{creationTime}, #{status})")
    @Options(useGeneratedKeys = true, keyProperty = "itemID")
    int add(Secondhanditem item);

    // 更新二手物品
    @Update("UPDATE secondhanditem SET sellerID = #{sellerID}, title = #{title}, description = #{description}, price = #{price}, creationTime = #{creationTime}, status = #{status} WHERE itemID = #{itemID}")
    int update(Secondhanditem item);

    // 更新二手物品
    @Update("UPDATE secondhanditem SET sellerID = #{sellerID}, title = #{title}, description = #{description}, price = #{price},status = #{status} WHERE itemID = #{itemID}")
    int update_noTime(Secondhanditem item);

    // 删除二手物品
    @Delete("DELETE FROM secondhanditem WHERE itemID = #{itemID}")
    int delete(int itemID);

    /**
     * 根据时间分页查询商品
     * @param start_time 开始时间
     * @param end_time 结束时间
     * @param offset 偏移量
     * @param pageSize 每页大小
     * @return 商品列表
     */
    @Select("SELECT * FROM secondhanditem WHERE creationTime BETWEEN #{start_time} AND #{end_time} ORDER BY creationTime DESC LIMIT #{offset}, #{pageSize}")
    List<Secondhanditem> getItemsByTime(String start_time, String end_time, int offset, int pageSize);

    /**
     * 根据时间查询商品总数
     * @param start_time 开始时间
     * @param end_time 结束时间
     * @return 商品总数
     */
    @Select("SELECT COUNT(*) FROM secondhanditem WHERE creationTime BETWEEN #{start_time} AND #{end_time}")
    int getItemsByTimeCount(String start_time, String end_time);

    /**
     * 通过关键词检索商品
     * @param keyword 关键词
     * @param offset 偏移量
     * @param pageSize 每页大小
     * @return 商品列表
     */
    @Select("SELECT * FROM secondhanditem WHERE title LIKE CONCAT('%', #{keyword}, '%') OR description LIKE CONCAT('%', #{keyword}, '%') ORDER BY creationTime DESC LIMIT #{offset}, #{pageSize}")
    List<Secondhanditem> searchItems(String keyword, int offset, int pageSize);

    /**
     * 通过关键词检索商品总数
     * @param keyword 关键词
     * @return 商品总数
     */
    @Select("SELECT COUNT(*) FROM secondhanditem WHERE title LIKE CONCAT('%', #{keyword}, '%') OR description LIKE CONCAT('%', #{keyword}, '%')")
    int searchItemsCount(String keyword);
}